package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import util.DBUtil;

import dao.UserDao;
import entity.User;

public class UserDaoJdbcImpl implements UserDao {

	@Override
	public int save(User user) throws Exception {
		String userName=user.getUserName();
		String passWord = user.getPassWord();
		String name = user.getName();
		int age=user.getAge();
		int gender = user.getGender();
		String phone=user.getPhone();
		
		String sql="insert into friend_user (username,password,name,age,gender,phone)" +
				" values(?,?,?,?,?,?)";
		Connection con=null;
		PreparedStatement pst = null;
		try {
			con=DBUtil.openInThread();
			pst=con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			pst.setString(1, userName);
			pst.setString(2, passWord);
			pst.setString(3, name);
			pst.setInt(4, age);
			pst.setInt(5, gender);
			pst.setString(6, phone);
			pst.execute();
			ResultSet rst = pst.getGeneratedKeys();
			int id=-1;
			if(rst.next()){
				id = rst.getInt(1);
			}
			DBUtil.close(null, pst, null);
			return id;
		} finally {
			DBUtil.closeInThread();
		}
	}

	@Override
	public User findByUserName(String userName) throws Exception {
		String sql="select * from friend_user where username=?";
		Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		try {
			con=DBUtil.openInThread();
			pst=con.prepareStatement(sql);
			pst.setString(1, userName);
			rs=pst.executeQuery();
			if(rs.next()){
				String passWord=rs.getString("password");
				int id=rs.getInt("id");
				User u = new User();
				u.setUserName(userName);
				u.setPassWord(passWord);
				u.setId(id);
				return u;
			}
			return null;
		} finally {
			DBUtil.close(null, pst, rs);
			DBUtil.closeInThread();
		}
	}

	@Override
	public List<User> findAll() throws Exception {
		List<User> users= new ArrayList<User>();
		String sql="select * from friend_user";
		Connection con=null;
		Statement stm=null;
		ResultSet rs=null;
		try {
			con=DBUtil.openInThread();
			stm=con.createStatement();
			rs=stm.executeQuery(sql);
			while(rs.next()){
				int id=rs.getInt("id");
				String userName=rs.getString("username");
				String name=rs.getString("name");
				int gender=rs.getInt("gender");
				int age=rs.getInt("age");
				String phone=rs.getString("phone");
				User u = new User(userName, null, name, age, gender, phone);
				u.setId(id);
				users.add(u);
			}
			DBUtil.close(null, stm, rs);
			return users;
		} finally {
			DBUtil.closeInThread();
		}
	}

	@Override
	public User findById(int id) throws Exception {
		String sql="select * from friend_user where id=?";
		Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		try {
			con=DBUtil.openInThread();
			pst=con.prepareStatement(sql);
			pst.setInt(1, id);
			rs=pst.executeQuery();
			if(rs.next()){
				String userName=rs.getString("username");
				String name=rs.getString("name");
				int gender=rs.getInt("gender");
				int age=rs.getInt("age");
				String phone=rs.getString("phone");
				User u = new User(userName, null, name, age, gender, phone);
				u.setId(id);
				return u;
			}
			return null;
		} finally {
			DBUtil.close(null, pst, rs);
			DBUtil.closeInThread();
		}
	}

	@Override
	public void delete(int id) throws Exception {
		String sql="delete from friend_user where id=?";
		Connection con=DBUtil.openInThread();
		PreparedStatement pst=con.prepareStatement(sql);
		pst.setInt(1, id);
		pst.execute();
		pst.close();
		DBUtil.closeInThread();
	}

	@Override
	public void update(User user) throws Exception {
		String sql="update friend_user set age=? , gender=? where id=? ";
		Connection con=DBUtil.openInThread();
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setInt(1, user.getId());
		pst.setInt(2, user.getAge());
		pst.setInt(3, user.getGender());
		pst.execute();
		pst.close();
		DBUtil.closeInThread();
	}

}
